package com.cul;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import com.sun.deploy.panel.ITreeNode;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelUtil {


    /**
     * excel转文件
     */
    private static   SqlFromExcelParam sqlFromExcelParam1 ;


	/**
	 * 根据配置和excel生成sql文件
	 * @param sqlFromExcelParam
	 */
	public static void generateSqlFromExcel(SqlFromExcelParam sqlFromExcelParam) {
        sqlFromExcelParam1 = sqlFromExcelParam;
        //获得表头坐标
        List<Integer> titleIndexList = getTileIndexList();



        //获得每行数据
        List<List<String>>   dataList = getDataList(titleIndexList);
        for (List<String> data : dataList) {
            System.out.println("data = [" + data + "]");
        }

        //数据写入到文件
        writeToSqlFile(dataList);


	}

    /**
     * 数据写入到文件
     * @param dataList
     */
    private static void writeToSqlFile(List<List<String>> dataList){
        FileModify fileModify = new FileModify();
        try {
            fileModify.fileAppender(sqlFromExcelParam1.getSqlFilepath(),"SET FOREIGN_KEY_CHECKS=0;");
            fileModify.fileAppender(sqlFromExcelParam1.getSqlFilepath(),"DROP TABLE IF EXISTS `"+sqlFromExcelParam1.getTableName()+"`;");
            fileModify.fileAppender(sqlFromExcelParam1.getSqlFilepath(),"CREATE TABLE `"+sqlFromExcelParam1.getTableName()+"` (");
            fileModify.fileAppender(sqlFromExcelParam1.getSqlFilepath()," `id` int(11) NOT NULL AUTO_INCREMENT,");
            for (List<String> data : dataList) {
                //字段名
                fileModify.fileAppender(sqlFromExcelParam1.getSqlFilepath(),"  `"+data.get(0)+"`  ");
                //字段类型
                fileModify.fileInLineAppender(sqlFromExcelParam1.getSqlFilepath(),data.get(1));

                //是否非空
                if("TRUE".equals(data.get(2))){
                    fileModify.fileInLineAppender(sqlFromExcelParam1.getSqlFilepath()," NOT NULL ");
                }
                if(null!=data.get(3)&&data.get(3).length()>0){
                    fileModify.fileInLineAppender(sqlFromExcelParam1.getSqlFilepath()," COMMENT '"+data.get(3)+"' ");
                }
                fileModify.fileInLineAppender(sqlFromExcelParam1.getSqlFilepath()," , ");
            }
            fileModify.fileAppender(sqlFromExcelParam1.getSqlFilepath()," PRIMARY KEY (`id`) ");

            fileModify.fileAppender(sqlFromExcelParam1.getSqlFilepath()," ) ENGINE=InnoDB DEFAULT CHARSET=utf8; ");

        } catch (IOException e) {
            e.printStackTrace();
        }


    }


    /**
     * 获得每行数据
     * @param titleIndexList
     * @return
     */
    private static List<List<String>> getDataList(List<Integer> titleIndexList) {

        List<List<String>> dataListRe = new ArrayList<List<String>>();
        for (int i = sqlFromExcelParam1.getStartRow(); i <= sqlFromExcelParam1.getEndRow(); i++) {
            List<String> subListRe = new ArrayList<String>();
            List<String>  subList = readExcel(i);
            //选中标定行
            for (Integer titleIndex : titleIndexList) {
                subListRe.add(subList.get(titleIndex));
            }
            dataListRe.add(subListRe);
        }
        return dataListRe;
    }

    /**
     * 获得表头坐标
     * @return
     */
    private static List<Integer> getTileIndexList() {
        List tileIndexList = new ArrayList<Integer>();
        List<String> titleRow = readExcel(sqlFromExcelParam1.getTitleRowNO());
        for (String excelTitleName :sqlFromExcelParam1.getTitleNames()){
            for (int i=0;i<titleRow.size();i++){
                String titleCell = titleRow.get(i);
                if (titleCell.equals(excelTitleName)){
                    tileIndexList.add(i);
                }
            }
        }
        return tileIndexList;
    }

    public static List<String> readExcel(Integer rowNO) {
        File excelFile = new File(sqlFromExcelParam1.getExcelFilepath());
        String fileName  = excelFile.getName();

        if (fileName.endsWith(".xls")) {
            return readXlsFile(excelFile,rowNO);
        }
        if (fileName.endsWith(".xlsx")) {
            return readXlsxFile(excelFile,rowNO);
        }
        return null;
    }


    private static List<String> readXlsxFile(File excelFile, Integer rowNO) {
        List listRe = new ArrayList<String>();
        try {
            InputStream inputStream = new FileInputStream(excelFile);
            XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
            XSSFSheet sheet = null;
            for (int i = 0; i < workbook.getNumberOfSheets(); i++) {//获取每个Sheet表
                XSSFSheet subSheet = workbook.getSheetAt(i);
                if (sqlFromExcelParam1.getSheetName().equals(subSheet.getSheetName())) {
                    sheet = subSheet;
                }
            }

            if (null == sheet) {
                return null;
            }
            int lastRowNum = sheet.getLastRowNum();
            if (rowNO > sheet.getLastRowNum()) {
                return null;
            }


            XSSFRow row;
            XSSFCell cell;
            String value = "";

            row = sheet.getRow(rowNO);
            int cellNum = row.getLastCellNum();// 列
            for (int j = 0; j < cellNum; j++) {// 对一行的每个列进行解析
                listRe.add( row.getCell(j).toString());
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return listRe;

    }




    private static List<String> readXlsFile(File excelFile,Integer rowNO) {
        List listRe = new ArrayList<String>();
        try {


            InputStream inputStream = new FileInputStream(excelFile);
            HSSFWorkbook workbook = new HSSFWorkbook(inputStream);

            HSSFSheet sheet = null;
            for (int i = 0; i < workbook.getNumberOfSheets(); i++) {//获取每个Sheet表
                HSSFSheet subSheet=workbook.getSheetAt(i);
                if (sqlFromExcelParam1.getSheetName().equals(subSheet.getSheetName())){
                    sheet = subSheet;
                }
            }
            if (null == sheet){return null;}
            int lastRowNum = sheet.getLastRowNum();
            if (rowNO>sheet.getLastRowNum()){return null;}
            int cellNum;
            HSSFRow row;
            HSSFCell cell;
            String value = "";
            row = sheet.getRow(rowNO);
            cellNum = row.getLastCellNum();// 列
            for (int j = 0; j < cellNum; j++) {// 对一行的每个列进行解析
                listRe.add(row.getCell(j).toString());
            }
        } catch (Exception e) {
            e.printStackTrace();
        }


        return listRe;
    }









}
